﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class ChuyenBay_DAO
    {
        //        CHUYENBAY        
        //MaCB varchar(9) not null,
        //SoGheHang1 int,
        //SoGheHang2 int,
        //GiaVeHang1 int,
        //GiaVeHang2 int,
        //GioDi datetime,
        //GioDen datetime,
        //primary key (MaCB)

        #region cac ham truy van du lieu

        public static List<ChuyenBay_DTO> layDanhSachChuyenBay()
        {
            string query = "select * from chuyenbay";

            List<ChuyenBay_DTO> list = new List<ChuyenBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay_DTO cb = new ChuyenBay_DTO();
                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());

                list.Add(cb);
            }
            return list;
        }

        //dung de xuat la list machuyen chuyen bay trong lich bay dang mo nhung chua bay
        //cho ng chon khi them ve
        public static List<ChuyenBay_DTO> layDanhSachChuyenBayDangMoLich()
        {
            string query = "select cb.* from chuyenbay cb join lichbay lb on cb.MaCB = lb.MaCB  where datediff(mi, getdate(), lb.NgayDi) >= 0 ";

            List<ChuyenBay_DTO> list = new List<ChuyenBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay_DTO cb = new ChuyenBay_DTO();
                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());

                list.Add(cb);
            }
            return list;
        }


        public static ChuyenBay_DTO layChuyenBayTheoMaChuyenBay(String maCB)
        {
            string query = "select * from chuyenbay where MaCB = '" + maCB + "'";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            ChuyenBay_DTO cb = new ChuyenBay_DTO();
            foreach (DataRow dr in dt.Rows)
            {

                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());


            }
            return cb;
        }

        public static List<ChuyenBay_DTO> layChuyenBayTheoSanBayDi(string sbDi)
        {
            string query = "select cb.* from chuyenbay cb , chitietsb ct, sanbay sb " +
                             "WHERE cb.Macb = ct.Macb and sb.masb = ct.masb and ct.loai = 1 and sb.tinh = '" + sbDi + "'";

            List<ChuyenBay_DTO> list = new List<ChuyenBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay_DTO cb = new ChuyenBay_DTO();
                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());

                list.Add(cb);
            }
            return list;
        }

        public static List<ChuyenBay_DTO> layChuyenBayTheoSanBayDen(string sbDen)
        {
            string query = "select cb.* from chuyenbay cb , chitietsb ct, sanbay sb " +
                              "WHERE cb.Macb = ct.Macb and sb.masb = ct.masb and ct.loai = 2 and sb.tinh = '" + sbDen + "' ";

            List<ChuyenBay_DTO> list = new List<ChuyenBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay_DTO cb = new ChuyenBay_DTO();
                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());

                list.Add(cb);
            }
            return list;
        }
        public static List<ChuyenBay_DTO> layChuyenBayTheoSanBayTGian(string sbtg)
        {
            string query = "select cb.* from chuyenbay cb , chitietsb ct, sanbay sb " +
                              "WHERE cb.Macb = ct.Macb and sb.masb = ct.masb and ct.loai = 3 and sb.tinh = '" + sbtg + "'";

            List<ChuyenBay_DTO> list = new List<ChuyenBay_DTO>();

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                ChuyenBay_DTO cb = new ChuyenBay_DTO();
                cb.giaHang1 = long.Parse(dr["GiaVeHang1"].ToString());
                cb.giaHang2 = long.Parse(dr["GiaVeHang2"].ToString());
                cb.gioDen = dr["GioDen"].ToString();
                cb.gioDi = dr["GioDi"].ToString();
                cb.maCB = dr["MaCB"].ToString();
                cb.soGheHang1 = int.Parse(dr["SoGheHang1"].ToString());
                cb.soGheHang2 = int.Parse(dr["SoGheHang2"].ToString());

                list.Add(cb);
            }
            return list;
        }
        #endregion

        #region Cac ham tuong tac voi co so du lieu

        //them chuyen bay
        public static bool themChuyenbay(ChuyenBay_DTO chuyenBay)  //them chuyen bay
        {
            string query = "insert into chuyenbay (MaCB, SoGheHang1, SoGheHang2, GiaVeHang1,GiaVeHang2,GioDi,GioDen) "
                + "values('" + chuyenBay.maCB + "', " + chuyenBay.soGheHang1 + ", " + chuyenBay.soGheHang2 + ", " + chuyenBay.giaHang1 + ", " + chuyenBay.giaHang2 + ",  '" + chuyenBay.gioDi + "', '" + chuyenBay.gioDen + "')";

            if (SqlDataAccessHelper.ExecuteNoneQuery(query) == 1)
                return true;

            return false;

        }

        public static bool ktThoiGianBayMinHopLe(ChuyenBay_DTO chuyenBay)
        {
            QuyDinh_DTO quydinh = new QuyDinh_DTO();
            quydinh = QuyDinh_DAO.layQuyDinh();

            if (doiChuoiGioSangPhut(chuyenBay.gioDen) - doiChuoiGioSangPhut(chuyenBay.gioDi) >= quydinh.thoiGianBayMin)
                return true;

            return false;
        }

        public static int doiChuoiGioSangPhut(string a)
        {
            string[] arr = a.Split(':');
            int tongPhut = int.Parse(arr[0]) * 60 + int.Parse(arr[1]);

            return tongPhut;
        }

        public static bool ktTonTaiChuyenBay(string MaCB)
        {
            string query = "select * from chuyenbay where MaCB = '" + MaCB + "'";

            DataTable dt = SqlDataAccessHelper.ExecuteQuery(query);

            if (dt.Rows.Count == 1)
                return true;
            return false;
        }

        public static bool suaChuyenBay(ChuyenBay_DTO chuyenBay)      // sua chuyen bay
        {
            string query = "update chuyenbay set  SoGheHang1 = " + chuyenBay.soGheHang1 + ", soGheHang2 = " + chuyenBay.soGheHang2 + ", GiaVeHang1 = " + chuyenBay.giaHang1 + ", GiaVeHang2 = " + chuyenBay.giaHang2 + ", GioDi = '" + chuyenBay.gioDi + "', GioDen = '" + chuyenBay.gioDen + "'"
                + " where MaCB = '" + chuyenBay.maCB + "'";

            if(SqlDataAccessHelper.ExecuteNoneQuery(query) == -1)
                return false;

                return true;
           
        }
        public static bool xoaChuyenBay(string MaCB)      // xoa chuyen bay
        {

            //xoá vé co chua malicbbay ma co machuyenbayđo
            string query0 = "delete VE  where Malb in (select lb.malb from lichbay lb where lb.macb = '" + MaCB + "' )";

            SqlDataAccessHelper.ExecuteNoneQuery(query0);
              

            //xoá chitiet co chua machuyenbay đo
            string query1 = "delete chitietsb where Macb = '" + MaCB + "'";

            SqlDataAccessHelper.ExecuteNoneQuery(query1);
                

            //xoa lịch bay co chua machuyenbay đo
            string query2 = "delete lichbay where Macb = '" + MaCB + "'";

            SqlDataAccessHelper.ExecuteNoneQuery(query2);
              

            
            string query = "delete chuyenbay where MaCB = '" + MaCB + "'";

            SqlDataAccessHelper.ExecuteNoneQuery(query);
               
                return true;
        }
        #endregion
    }
}
